package com.project.myPackage.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.project.myPackage.entity.Orderdetails;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface OrderdetailsMapper extends BaseMapper<Orderdetails> {
    //根据订单id获取所有订单详情信息关联商品信息
    @Select("SELECT od.*, p.* FROM orderdetails od " +
            "JOIN products p ON od.product_id = p.product_id " +
            "WHERE od.order_id = #{order_id}")
  List<Map<String, Object>> getAllOrderdetailsByOrderId(Integer order_id);

    //根据订单详情统计商品分类下的销售总额可以根据时间段来筛选
  @Select("<script>" +
          "SELECT p.category_id, p.category_name, SUM(od.price * od.quantity) AS total_money " +
          "FROM orderdetails od " +
          "JOIN products p ON od.product_id = p.product_id " +
          "WHERE 1=1 " +
          "<if test='start_time != null'>AND od.created_at &gt;= #{start_time}</if> " +
          "<if test='end_time != null'>AND od.created_at &lt;= #{end_time}</if> " +
          "GROUP BY p.category_id, p.category_name " +
          "</script>")
  List<Map<String, Object>>  getTotalCategoryMoney(String start_time, String  end_time);



    // 根据订单日期统计每日下的销售额 可根据日期来筛选
    @Select("<script>" +
            "SELECT DATE_FORMAT(od.created_at, '%Y-%m-%d') AS order_date, SUM(od.price * od.quantity) AS total_money " +
            "FROM orderdetails od " +
            "WHERE 1=1 " +
            "<if test='start_time != null'>AND od.created_at &gt;= #{start_time}</if> " +
            "<if test='end_time != null'>AND od.created_at &lt; #{end_time} + INTERVAL 1 DAY</if> " +
            "GROUP BY DATE_FORMAT(od.created_at, '%Y-%m-%d') " +
            "ORDER BY DATE_FORMAT(od.created_at, '%Y-%m-%d') " +
            "</script>")
    List<Map<String, Object>> getTotalMoneyByOrderDate(String start_time, String end_time);
}
